
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/02/2011 13:21:13
-- Generated from EDMX file: C:\Users\Darky\Desktop\DigDes\solution\InformationBoard\Server\DataModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [InformationBoard];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ClassesOfPassengersClientDevices]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClientDevices] DROP CONSTRAINT [FK_ClassesOfPassengersClientDevices];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientChangesClientDevices]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClientChanges] DROP CONSTRAINT [FK_ClientChangesClientDevices];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientDevicesLocation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClientDevices] DROP CONSTRAINT [FK_ClientDevicesLocation];
GO
IF OBJECT_ID(N'[dbo].[FK_PlaylistClientDevices]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Playlists] DROP CONSTRAINT [FK_PlaylistClientDevices];
GO
IF OBJECT_ID(N'[dbo].[FK_PlaylistDaysOfWeek]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Playlists] DROP CONSTRAINT [FK_PlaylistDaysOfWeek];
GO
IF OBJECT_ID(N'[dbo].[FK_LocationSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Schedules] DROP CONSTRAINT [FK_LocationSchedule];
GO
IF OBJECT_ID(N'[dbo].[FK_LocationScheduleChange]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScheduleChanges] DROP CONSTRAINT [FK_LocationScheduleChange];
GO
IF OBJECT_ID(N'[dbo].[FK_StationLocation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Locations] DROP CONSTRAINT [FK_StationLocation];
GO
IF OBJECT_ID(N'[dbo].[FK_PlaylistVideo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Playlists] DROP CONSTRAINT [FK_PlaylistVideo];
GO
IF OBJECT_ID(N'[dbo].[FK_DaysOfWeekSchedule_DaysOfWeek]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DaysOfWeekSchedule] DROP CONSTRAINT [FK_DaysOfWeekSchedule_DaysOfWeek];
GO
IF OBJECT_ID(N'[dbo].[FK_DaysOfWeekSchedule_Schedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DaysOfWeekSchedule] DROP CONSTRAINT [FK_DaysOfWeekSchedule_Schedule];
GO
IF OBJECT_ID(N'[dbo].[FK_ScheduleScheduleChanx]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScheduleChanges] DROP CONSTRAINT [FK_ScheduleScheduleChanx];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientDeviceScreenShot]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScreenShots] DROP CONSTRAINT [FK_ClientDeviceScreenShot];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[ClassesOfPassengers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClassesOfPassengers];
GO
IF OBJECT_ID(N'[dbo].[ClientChanges]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClientChanges];
GO
IF OBJECT_ID(N'[dbo].[ClientDevices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClientDevices];
GO
IF OBJECT_ID(N'[dbo].[DaysOfWeeks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DaysOfWeeks];
GO
IF OBJECT_ID(N'[dbo].[Locations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Locations];
GO
IF OBJECT_ID(N'[dbo].[Playlists]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Playlists];
GO
IF OBJECT_ID(N'[dbo].[ScheduleChanges]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScheduleChanges];
GO
IF OBJECT_ID(N'[dbo].[Schedules]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Schedules];
GO
IF OBJECT_ID(N'[dbo].[Stations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Stations];
GO
IF OBJECT_ID(N'[dbo].[Videos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Videos];
GO
IF OBJECT_ID(N'[dbo].[ScreenShots]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScreenShots];
GO
IF OBJECT_ID(N'[dbo].[DaysOfWeekSchedule]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DaysOfWeekSchedule];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ClassesOfPassengers'
CREATE TABLE [dbo].[ClassesOfPassengers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ClientChanges'
CREATE TABLE [dbo].[ClientChanges] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [LastPing] datetime  NULL,
    [ClientDevice_Id] int  NOT NULL,
    [SchedulesDataVersion] int  NOT NULL,
    [VideoDataVersion] int  NOT NULL,
    [SettingsDataVersion] int  NOT NULL
);
GO

-- Creating table 'ClientDevices'
CREATE TABLE [dbo].[ClientDevices] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PossibilityOfUpdateVideo] bit  NOT NULL,
    [PingFrequency] int  NOT NULL,
    [ScreenshotFrequency] int  NOT NULL,
    [VideoInterruptFrequency] int  NOT NULL,
    [ClassesOfPassengersId] int  NOT NULL,
    [Location_Id] int  NOT NULL,
    [Ticker] nvarchar(max)  NULL
);
GO

-- Creating table 'DaysOfWeeks'
CREATE TABLE [dbo].[DaysOfWeeks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Locations'
CREATE TABLE [dbo].[Locations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [StationId] int  NOT NULL
);
GO

-- Creating table 'Playlists'
CREATE TABLE [dbo].[Playlists] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TimeOfBegin] datetime  NOT NULL,
    [TimeOfEnd] datetime  NOT NULL,
    [Video_Id] int  NOT NULL,
    [ClientDevice_Id] int  NOT NULL,
    [DaysOfWeek_Id] int  NOT NULL
);
GO

-- Creating table 'ScheduleChanges'
CREATE TABLE [dbo].[ScheduleChanges] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DateOfChange] datetime  NOT NULL,
    [TimeOfDeparture] time  NOT NULL,
    [StationOfDestination] nvarchar(max)  NOT NULL,
    [NumberOfTransport] nvarchar(max)  NOT NULL,
    [LocationId] int  NOT NULL,
    [Canceled] bit  NOT NULL,
    [TimeOfLanding] time  NOT NULL,
    [ScheduleId] int  NULL
);
GO

-- Creating table 'Schedules'
CREATE TABLE [dbo].[Schedules] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TimeOfDeparture] time  NOT NULL,
    [StationOfDestination] nvarchar(max)  NOT NULL,
    [NumberOfTransport] nvarchar(max)  NOT NULL,
    [LocationId] int  NOT NULL,
    [TimeOfLanding] time  NOT NULL
);
GO

-- Creating table 'Stations'
CREATE TABLE [dbo].[Stations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Videos'
CREATE TABLE [dbo].[Videos] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FileName] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ScreenShots'
CREATE TABLE [dbo].[ScreenShots] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TimeOfShot] datetime  NOT NULL,
    [FileName] nvarchar(max)  NOT NULL,
    [ClientDeviceId] int  NOT NULL
);
GO

-- Creating table 'DaysOfWeekSchedule'
CREATE TABLE [dbo].[DaysOfWeekSchedule] (
    [DaysOfWeek_Id] int  NOT NULL,
    [Schedule_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'ClassesOfPassengers'
ALTER TABLE [dbo].[ClassesOfPassengers]
ADD CONSTRAINT [PK_ClassesOfPassengers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ClientChanges'
ALTER TABLE [dbo].[ClientChanges]
ADD CONSTRAINT [PK_ClientChanges]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ClientDevices'
ALTER TABLE [dbo].[ClientDevices]
ADD CONSTRAINT [PK_ClientDevices]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DaysOfWeeks'
ALTER TABLE [dbo].[DaysOfWeeks]
ADD CONSTRAINT [PK_DaysOfWeeks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Locations'
ALTER TABLE [dbo].[Locations]
ADD CONSTRAINT [PK_Locations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Playlists'
ALTER TABLE [dbo].[Playlists]
ADD CONSTRAINT [PK_Playlists]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ScheduleChanges'
ALTER TABLE [dbo].[ScheduleChanges]
ADD CONSTRAINT [PK_ScheduleChanges]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Schedules'
ALTER TABLE [dbo].[Schedules]
ADD CONSTRAINT [PK_Schedules]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Stations'
ALTER TABLE [dbo].[Stations]
ADD CONSTRAINT [PK_Stations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Videos'
ALTER TABLE [dbo].[Videos]
ADD CONSTRAINT [PK_Videos]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ScreenShots'
ALTER TABLE [dbo].[ScreenShots]
ADD CONSTRAINT [PK_ScreenShots]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [DaysOfWeek_Id], [Schedule_Id] in table 'DaysOfWeekSchedule'
ALTER TABLE [dbo].[DaysOfWeekSchedule]
ADD CONSTRAINT [PK_DaysOfWeekSchedule]
    PRIMARY KEY NONCLUSTERED ([DaysOfWeek_Id], [Schedule_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ClassesOfPassengersId] in table 'ClientDevices'
ALTER TABLE [dbo].[ClientDevices]
ADD CONSTRAINT [FK_ClassesOfPassengersClientDevices]
    FOREIGN KEY ([ClassesOfPassengersId])
    REFERENCES [dbo].[ClassesOfPassengers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClassesOfPassengersClientDevices'
CREATE INDEX [IX_FK_ClassesOfPassengersClientDevices]
ON [dbo].[ClientDevices]
    ([ClassesOfPassengersId]);
GO

-- Creating foreign key on [ClientDevice_Id] in table 'ClientChanges'
ALTER TABLE [dbo].[ClientChanges]
ADD CONSTRAINT [FK_ClientChangesClientDevices]
    FOREIGN KEY ([ClientDevice_Id])
    REFERENCES [dbo].[ClientDevices]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientChangesClientDevices'
CREATE INDEX [IX_FK_ClientChangesClientDevices]
ON [dbo].[ClientChanges]
    ([ClientDevice_Id]);
GO

-- Creating foreign key on [Location_Id] in table 'ClientDevices'
ALTER TABLE [dbo].[ClientDevices]
ADD CONSTRAINT [FK_ClientDevicesLocation]
    FOREIGN KEY ([Location_Id])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientDevicesLocation'
CREATE INDEX [IX_FK_ClientDevicesLocation]
ON [dbo].[ClientDevices]
    ([Location_Id]);
GO

-- Creating foreign key on [ClientDevice_Id] in table 'Playlists'
ALTER TABLE [dbo].[Playlists]
ADD CONSTRAINT [FK_PlaylistClientDevices]
    FOREIGN KEY ([ClientDevice_Id])
    REFERENCES [dbo].[ClientDevices]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlaylistClientDevices'
CREATE INDEX [IX_FK_PlaylistClientDevices]
ON [dbo].[Playlists]
    ([ClientDevice_Id]);
GO

-- Creating foreign key on [DaysOfWeek_Id] in table 'Playlists'
ALTER TABLE [dbo].[Playlists]
ADD CONSTRAINT [FK_PlaylistDaysOfWeek]
    FOREIGN KEY ([DaysOfWeek_Id])
    REFERENCES [dbo].[DaysOfWeeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlaylistDaysOfWeek'
CREATE INDEX [IX_FK_PlaylistDaysOfWeek]
ON [dbo].[Playlists]
    ([DaysOfWeek_Id]);
GO

-- Creating foreign key on [LocationId] in table 'Schedules'
ALTER TABLE [dbo].[Schedules]
ADD CONSTRAINT [FK_LocationSchedule]
    FOREIGN KEY ([LocationId])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocationSchedule'
CREATE INDEX [IX_FK_LocationSchedule]
ON [dbo].[Schedules]
    ([LocationId]);
GO

-- Creating foreign key on [LocationId] in table 'ScheduleChanges'
ALTER TABLE [dbo].[ScheduleChanges]
ADD CONSTRAINT [FK_LocationScheduleChange]
    FOREIGN KEY ([LocationId])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocationScheduleChange'
CREATE INDEX [IX_FK_LocationScheduleChange]
ON [dbo].[ScheduleChanges]
    ([LocationId]);
GO

-- Creating foreign key on [StationId] in table 'Locations'
ALTER TABLE [dbo].[Locations]
ADD CONSTRAINT [FK_StationLocation]
    FOREIGN KEY ([StationId])
    REFERENCES [dbo].[Stations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StationLocation'
CREATE INDEX [IX_FK_StationLocation]
ON [dbo].[Locations]
    ([StationId]);
GO

-- Creating foreign key on [Video_Id] in table 'Playlists'
ALTER TABLE [dbo].[Playlists]
ADD CONSTRAINT [FK_PlaylistVideo]
    FOREIGN KEY ([Video_Id])
    REFERENCES [dbo].[Videos]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlaylistVideo'
CREATE INDEX [IX_FK_PlaylistVideo]
ON [dbo].[Playlists]
    ([Video_Id]);
GO

-- Creating foreign key on [DaysOfWeek_Id] in table 'DaysOfWeekSchedule'
ALTER TABLE [dbo].[DaysOfWeekSchedule]
ADD CONSTRAINT [FK_DaysOfWeekSchedule_DaysOfWeek]
    FOREIGN KEY ([DaysOfWeek_Id])
    REFERENCES [dbo].[DaysOfWeeks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Schedule_Id] in table 'DaysOfWeekSchedule'
ALTER TABLE [dbo].[DaysOfWeekSchedule]
ADD CONSTRAINT [FK_DaysOfWeekSchedule_Schedule]
    FOREIGN KEY ([Schedule_Id])
    REFERENCES [dbo].[Schedules]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DaysOfWeekSchedule_Schedule'
CREATE INDEX [IX_FK_DaysOfWeekSchedule_Schedule]
ON [dbo].[DaysOfWeekSchedule]
    ([Schedule_Id]);
GO

-- Creating foreign key on [ScheduleId] in table 'ScheduleChanges'
ALTER TABLE [dbo].[ScheduleChanges]
ADD CONSTRAINT [FK_ScheduleScheduleChanx]
    FOREIGN KEY ([ScheduleId])
    REFERENCES [dbo].[Schedules]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScheduleScheduleChanx'
CREATE INDEX [IX_FK_ScheduleScheduleChanx]
ON [dbo].[ScheduleChanges]
    ([ScheduleId]);
GO

-- Creating foreign key on [ClientDeviceId] in table 'ScreenShots'
ALTER TABLE [dbo].[ScreenShots]
ADD CONSTRAINT [FK_ClientDeviceScreenShot]
    FOREIGN KEY ([ClientDeviceId])
    REFERENCES [dbo].[ClientDevices]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientDeviceScreenShot'
CREATE INDEX [IX_FK_ClientDeviceScreenShot]
ON [dbo].[ScreenShots]
    ([ClientDeviceId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------